pp108 : Using Database Related APIs

Using Database Related APIs
This topic describes the APIs used in working with relational databases.

Creating or Modifying Database Objects

Example The following is an example of the SOAP request to create a table with two fields in the MS SQL database:

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
        <ExecuteDDL xmlns="http://schemas.cordys.com/1.1/metadata/oledb"> CREATE TABLE TestTable(Column1 INT,Column2 INT,PRIMARY KEY(Column1)) </ExecuteDDL>
    </SOAP:Body>
</SOAP:Envelope>

As the DDL execution does not require retrieval of data, no data is retrieved from the database. Following is the sample response that is received for the above query:

<data>
    <ExecuteDDLResponse xmlns="http://schemas.cordys.com/1.1/metadata/oledb"> CREATE TABLE TestTable(Column1 INT,Column2 INT,PRIMARY KEY(column1)) </ExecuteDDLResponse>
</data>


Similar commands for other database objects such as procedures, views, indices, and constraints, can be used in the ExecuteDDL Web service operation.

Note:

  • At least one Update connection is expected in the Service Container's configuration for the Web service operation to be successfully executed by the Service Container.
  • The capability of the Web service operation depends on the corresponding database's DDL support.
  • Only the administrator can set the security for this feature at the Service Group level, namespace level, and Web service operation level.
  • This Web service operation does not support statements (e.g., TRUNCATE TABLE), in which the commands operate on the contents of the database object.

Querying a Database


Note: The database connector also supports dynamic queries (queries with dynamic filter and sort). Refer to Dynamic Queries.

Simple Query: The following is a SOAP request to retrieve a particular record of the Employees table of the Northwind database in the SQL Server, which passes EmployeeID as parameter.

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
        <GetEmployee xmlns="http://schemas.cordys.com/1.0/demo/northwind">
            <EmployeeID>1</EmployeeID>
        </GetEmployee>
    </SOAP:Body>
</SOAP:Envelope>


Query to retrieve a set of records: The following is a SOAP request to retrieve a set of records from the same Employees table.

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
        <GetEmployees xmlns="http://schemas.cordys.com/1.0/demo/northwind">
            <fromEmployeeID>0</fromEmployeeID>
            <toEmployeeID>9999</toEmployeeID>
        </GetEmployees>
    </SOAP:Body>
</SOAP:Envelope>


Query for restricting number of rows: The following is a SOAP request to retrieve records from a specified number of rows.

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
        <GetEmployees xmlns="http://schemas.cordys.com/1.0/demo/northwind">
            <fromEmployeeID>1</fromEmployeeID>
            <toEmployeeID>10</toEmployeeID>
            <cursor numRows="2"/>
        </GetEmployees>
    </SOAP:Body>
</SOAP:Envelope>


The above request returns 2 rows at a time. The response is as follows:

<GetEmployeesResponse xmlns="http://schemas.cordys.com/1.0/demo/northwind">
    <cursor id="1.2" maxRows="99999997" numRows="2" position="2"/>
    <tuple>
        <old>
            <Employees>
                <EmployeeID>1</EmployeeID>
                <LastName>Davolio</LastName>
                <FirstName>Nancy</FirstName>
                <Title>Medical Representative</Title>
                <TitleOfCourtesy>Ms.</TitleOfCourtesy>
                <BirthDate>1992-05-17T00:00:00</BirthDate>
                <HireDate>1993-06-02T00:00:00</HireDate>
                <Address>507 - 20th Ave. E.Apt. 2A</Address>
                <City>Seattle</City>
                <Region>WA</Region>
                <PostalCode>98122</PostalCode>
                <Country>USA</Country>
                <HomePhone>(71) 555 - 5577</HomePhone>
                <Extension>5467</Extension>
            </Employees>
        </old>
    </tuple>
    <tuple>
        <old>
            <Employees>
                <EmployeeID>2</EmployeeID>
                <LastName>Fuller</LastName>
                <FirstName>Andrew</FirstName>
                <Title>Vice President, Sales</Title>
                <TitleOfCourtesy>Dr.</TitleOfCourtesy>
                <BirthDate>1952-02-19T00:00:00</BirthDate>
                <HireDate>1992-08-14T00:00:00</HireDate>
                <Address>908 W. Capital Way</Address>
                <City>Tacoma</City>
                <Region>WA</Region>
                <PostalCode>98401</PostalCode>
                <Country>USA</Country>
                <HomePhone>(206) 555-9482</HomePhone>
                <Extension>3457</Extension>
            </Employees>
        </old>
    </tuple>
</GetEmployeesResponse>


The following request retrieves the next two lines:

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
        <GetEmployees xmlns="http://schemas.cordys.com/1.0/demo/northwind">
            <fromEmployeeID>1</fromEmployeeID>
            <toEmployeeID>10</toEmployeeID>
            <cursor id="1.2" numRows="2" position="2"/>
        </GetEmployees>
    </SOAP:Body>
</SOAP:Envelope>


Note: The ID and position should be specified as they appear in the response. In the above SOAP messages, the cursor tag has the following attributes:

  • Each read connection has its own set of cursors (and cursor-ids), which is independent of other read connections. When the user specifies theidattribute for a cursor, an attempt is made to get the previously executed query corresponding to the cursor id. In case of multiple Read connections, the request may go to any of the free Read connections. Therefore, cursors cannot be used with theidattribute when multiple Read connections are present.
  • Thepositionattribute determines the number of rows to be skipped before fetching the data. Negative position values are invalid and previously fetched rows cannot be fetched again.
  • Each cursor maintains a current position of the result. If the cursor id is specified but the position is not, it will fetch rows (number of rows fetched is specified by thenumRowsattribute, if present) from the position maintained internally by the cursor.
  • If the user has prior knowledge of the position of the record in the table, thepositionattribute can be used to specify the record(s) can be retrieved.
  • When only the
    position attribute is specified in the cursor tag ( and not the id attribute), it is treated as a new query. Therefore, in spite of multiple Read connections, the rows fetched will be the same.
  • ThemaxRowstag determines the maximum number of rows that must be included in the response. If the value of thenumRowstag is greater than the value of themaxRowstag, the value of themaxRowstag takes precedence.
  • By default Process Platform database layer retrieves 5000 rows at a time. This is done to avoid overloading of resources. If the application needs to retrieve more than 5000 rows, then cursor requests can be used.Eg: <cursor numRows='6000'/>

Preserving White Spaces

It is possible to preserve white spaces by using the preserveSpace attribute. If this attribute is set to true, the white spaces are preserved at the end of data in the response. Otherwise, it does not preserve the white spaces, which is also the default behavior.The following is a sample read request implementation:

<implementation type="DBSQL" xmlns="">
    <constructor language="DBSQL" preserveSpace="true">
        <query>SELECT * FROM Employees</query>
    </constructor>
</implementation>



Note: Also refer to Additional Features Provided for Querying a Database.

Updating a Database


When using Process Platform to update a database table, for all update transactions (such as insert, update and delete) to work properly, a primary key or a unique index must be defined on the table. It is mandatory to send the primary key or the unique index (if primary key is not defined) in the update request.

The message for updating a database is given below:

<update xmlns="namespace">
    <tuple>
        <old>
            <tablename>
                <fieldname1>fieldvalue1</fieldname1>
                <fieldname2>fieldvalue2</fieldname2>
            </tablename>
        </old>
        <new>
            <tablename>
                <fieldname1>fieldvalue1</fieldname1>
                <fieldname2>fieldvalue2</fieldname2>
            </tablename>
        </new>
    </tuple>
</update>


Here, the field names should be specified in the same case as they appear in the back-end.

Note: If optimistic locking is required, the old values must be sent in the request. If the current state of the database is the same as that of the old value in the SOAP message, the record is modified. If the old value does not match the current state of the database, it implies that the database has been changed by another user and an error occurs. For example, if the old value is 10 and the current state of the database is 10.0, an error will be shown because an XML-based comparison and not a string comparison is used. If optimistic locking is not required, then only the primary key or the unique index must be sent. If both are defined on the table, the primary key must be sent.
The following is a sample request to update the value of the FirstName field from Nancy to Nancys.

<update xmlns="http://schemas.cordys.com/1.0/demo/northwind">
    <tuple>
        <old>
            <Employees>
                <EmployeeID>1</EmployeeID>
                <FirstName>Nancy</FirstName>
                <LastName>Davolio</LastName>
            </Employees>
        </old>
        <new>
            <Employees>
                <EmployeeID>1</EmployeeID>
                <FirstName>Nancys</FirstName>
                <LastName>Davolio</LastName>
            </Employees>
        </new>
    </tuple>
</update>


The message for inserting data into a database is given below:

<update>
    <tuple>
        <new>
            <table1>
                <column1>test1</column1>
                <column2>test2</column2>
                <column3>test3</column3>
            </table1>
        </new>
    </tuple>
</update>


The above message:

  • inserts the data into database
  • reads the data of the inserted row from the database
  • shows the row contents to the user


Hence, in this process, a database is read after each insert in order to retrieve all column values of the inserted row. Even if a few column values are inserted, all column values are retrieved from the database and are shown to the user.

In case of bulk insertions, the user may not wish to see the inserted row information every time. In such cases, the user can skip the database reading operation by adding an attributereplywith value 'no' to the update Web service operation implementation stored in the LDAP. The implementation of the update Web service operation in the LDAP is given below:

<implementation type="DBSQL">
    <update reply="no"/>
</implementation>


Note: WS-AppServer connector support inserts to tables with an auto-generated field as the primary key, only if the JDBC driver being used supports the retrieval of such fields. If the JDBC driver does not support the feature, adding the reply attribute with value 'no' in the 'update' Web service operation implementation will enable inserts. The following is a sample request to insert a new record into the
Employees database, with a database read after every insertion.

<update xmlns="http://schemas.cordys.com/1.0/demo/northwind">
    <tuple>
        <new>
            <Employees>
                <FirstName>Jennifer</FirstName>
                <LastName>John</LastName>
            </Employees>
        </new>
    </tuple>
</update>


Additionally, any field in an insert request can also contain an attribute calledguid. This attribute is Boolean and can be set to 'true' or 'false'. This attribute is used to specify whether the field (column) is a Global Unique Identifier (GUID). When this is specified in the request, the connector automatically creates the GUID value while inserting the record. The GUID format is {7F892C25-0E2A-4475-8022-BE8DD70426F6}, where the braces and hyphens are also part of the GUID.

If a field has empty value and theguidattribute is set to 'true', a unique identifier is inserted as data by the connector. Even if the reply attribute is set to 'no', the value of GUID is available to the user in the response.

Consider the following sample request with the guid attribute:

<tuple>
    <new>
        <TestUid>
            <col1 guid="true"/>
            <col2>abc</col2>
        </TestUid>
    </new>
</tuple>


The response for the above would be:

<tuple>
    <new>
        <TestUid>
            <col1>\{7F892C25-0E2A-4475-8022-BE8DD70426F6\}</col1>
            <col2>abc</col2>
        </TestUid>
    </new>
</tuple>

Deleting a Record

For deleting a record from the database, the request message is built with the values for the record that has to be deleted.

<update xmlns="http://schemas.cordys.com/1.0/demo/northwind">
    <tuple>
        <old>
            <tablename>
                <fieldname1>fieldvalue1</fieldname1>
                <fieldname2>fieldvalue2</fieldname2>
            </tablename>
        </old>
    </tuple>
</update>

For example, the following request message deletes a record from the Employees database:

<update xmlns="http://schemas.cordys.com/1.0/demo/northwind">
    <tuple>
        <old>
            <Employees>
                <EmployeeID>17</EmployeeID>
                <FirstName>Jennifer</FirstName>
                <LastName>John</LastName>
            </Employees>
        </old>
    </tuple>
</update>


Note: For updates of LOB type fields which are NOT NULL, the WS-AppServer connector supports command-based updates. For this, the update Web service operation implementation should contain the commandUpdate='yes' attribute.

<implementation type="DBSQL">
    <update commandUpdate="yes"/>
</implementation>

If an update request contains multiple for the same table, then that request can be sent as a batch update to the database server.

Performing Batch Updates


Batch update enables users to update same set of columns for multiple records, in a database table. You can send multiple updates (insert, modify or delete operations) in an Update request, to be executed as a batch rather than sending these updates separately.
To enable batch update, implementation of update Web service operation should contain the batchUpdate attribute set to 'yes'. Batch update improves the performance of the WS-AppServer connector. However, it is not possible to perform multiple updates (insert, modify and delete) for a specific record, in a single Update request, when a batch update is enabled.

<implementation type="DBSQL">
    <update batchUpdate="yes"/>
</implementation>


Note: To insert rows into a dabatabase table using batch update in combination with auto or database generated primary keys, ensure to set the reply parameter to no in the implementation of the update Web service operation.

Validating a Query


To validate a query, the validate Web service operation of Process Platform is used. This Web service operation takes a collection of queries for validation and returns the metadata if the query is valid and an error if the query is invalid.

The following is an example of an invalid query that is passed for validation:

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
        <validate xmlns="http://schemas.cordys.com/1.1/metadata/oledb">
            <query>SELECT Sum(EmployeeID) SumEmpID,EmployeeID FROM Employees</query>
        </validate>
    </SOAP:Body>
</SOAP:Envelope>


The above query is invalid because the parameterEmployeeID is not in the expression. Below is the SOAP:Fault returned by the back end.

<SOAP:Fault table="employees" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:faultcode>Server.ValidationError</SOAP:faultcode>
    <SOAP:faultstring>Query Validation failed</SOAP:faultstring>
    <SOAP:detail>
        <validate xmlns="http://schemas.cordys.com/1.1/metadata/oledb">
            <query>SELECT Sum(EmployeeID) SumEmpID ,EmployeeID FROM Employees</query>
        </validate>
        <error TYPE="Enumeration" xmlns="http://schemas.cordys.com/1.1/metadata/oledb">
            <elem>Prepare (SELECT Sum(EmployeeID) SumEmpID ,EmployeeID FROM Employees) failed</elem>
            <elem>GetColumnsRowset for IColumnsRowset failed: hresult: 0x80040e14 (DB_E_ERRORSINCOMMAND)</elem>
            <elem>GetErrorInfo failed</elem>
            <elem>prepare for command failed</elem>
        </error>
    </SOAP:detail>
</SOAP:Fault>

The EmployeeID field is then added to the GROUP BY clause and the query is modified as follows:

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
        <validate xmlns="http://schemas.cordys.com/1.1/metadata/oledb">
            <query>SELECT Sum(EmployeeID) SumEmpID,EmployeeID FROM Employees GROUP BY EmployeeID</query>
        </validate>
    </SOAP:Body>
</SOAP:Envelope>

The above query returns the following response:

<validateResponse table="Employees" xmlns="http://schemas.cordys.com/1.1/metadata/oledb">
    <validate>
        <query>SELECT Sum(EmployeeID) SumEmpID ,EmployeeID FROM Employees GROUP BY EmployeeID</query>
        <result>
            <tuple>
                <DBCOLUMN_NAME>SumEmpID</DBCOLUMN_NAME>
				<DBCOLUMN_BASETABLENAME>Employees</DBCOLUMN_BASETABLENAME>
          		<DBCOLUMN_BASECATALOGNAME />
          		<DBCOLUMN_SCHEMANAME />
                <DBCOLUMN_LABELNAME>SumCol1</DBCOLUMN_LABELNAME>
          		<DBCOLUMN_DBTYPE_NAME>int</DBCOLUMN_DBTYPE_NAME>
          		<DBCOLUMN_TYPE>i4</DBCOLUMN_TYPE>
          		<DBCOLUMN_PRECISION>10</DBCOLUMN_PRECISION>
          		<DBCOLUMN_SCALE>0</DBCOLUMN_SCALE>
          		<DBCOLUMN_COLUMNSIZE>11</DBCOLUMN_COLUMNSIZE>
          		<DBCOLUMN_NULLABLE>columnNullable</DBCOLUMN_NULLABLE>   
           </tuple>
            <tuple>
                <DBCOLUMN_NAME>EmployeeID</DBCOLUMN_NAME>
                <DBCOLUMN_BASETABLENAME>Employees</DBCOLUMN_BASETABLENAME>
          		<DBCOLUMN_BASECATALOGNAME />
          		<DBCOLUMN_SCHEMANAME />
				<DBCOLUMN_LABELNAME>EmployeeID</DBCOLUMN_LABELNAME>
                <DBCOLUMN_DBTYPE_NAME>int</DBCOLUMN_DBTYPE_NAME>
          		<DBCOLUMN_TYPE>i4</DBCOLUMN_TYPE>
          		<DBCOLUMN_PRECISION>10</DBCOLUMN_PRECISION>
          		<DBCOLUMN_SCALE>0</DBCOLUMN_SCALE>
          		<DBCOLUMN_COLUMNSIZE>11</DBCOLUMN_COLUMNSIZE>
				<DBCOLUMN_NULLABLE>columnNoNulls</DBCOLUMN_NULLABLE>
				<DBCOLUMN_AUTOINCREMENT>true</DBCOLUMN_AUTOINCREMENT>
            </tuple>
        </result>
    </validate>
</validateResponse>

Retrieving Data Source Information


The following request is sent to retrieve the data source information:

<tuple>
    <old>
        <BackEndInfo>
            <dbProductName>Microsoft SQL Server</dbProductName>
            <dbProductVersion>08.00.0194</dbProductVersion>
            <connector>OLEDB</connector>
        </BackEndInfo>
    </old>
</tuple>


The response contains the following tags:

  • dbProductName: This is the name of the database product being used.
  • dbProductVersion: This is the version of the database product being used.
  • connector: This is the database connector being used.

Handling Aggregated Updates and Associated Inserts


In the default implementation, the database connector supports multiple tuples in an update request and each tuple is independent of the other. Moreover, there are no relations between fields within a tuple, or fields from different tuples. Now consider the Northwind database and an application where the user needs to insert an Orders record and OrderDetails record(s) in one transaction. This is not possible in the default implementation of the update Web service operation, as there is a need to define a relationship between the Orders table and the OrderDetails table in a single request, to provide transactional context . To provide this support, 'associated insertion' and 'aggregated updates' are introduced.

Associated insertion deals with the relationship of fields in two tuples. Aggregated updates deals with the relationship of fields in two different tables within the same tuple. The update tag in the implementation has an attribute calledrelated. This attribute specifies whether the Web service operation is associated or aggregated. By default, the value of this attribute is 'no' and the update request is treated as a normal update request. In the case of aggregated updates and associated inserts, this attribute should be set to 'yes'.
The tags used in the request format for an aggregated update or associated insert are explained in the following table:

Tags Description
tuple The XML element corresponding to 'tuple' in the update request
old The XML element corresponding to 'old' in the update request
new The XML element corresponding to 'new' in the update request
table The XML element corresponding to a table in the update request
base-table-tag The first <table> tag under the old or new tags
field The XML element corresponding to a column of a table in the update request



An associated insert request contains a set of tuples where a field in one tuple can refer to a field in another tuple, only if the former tuple is the right sibling of the latter. Moreover, the referred field must be present in the base table. An aggregated update (insertion/update/deletion) comprises nested table structures in a tuple, and the fields of one inner table refer to the fields of the outer table.

The following table lists the attributes used with the various tags of the update request:

Attribute Description
src This can be set at any field under a non-base-table tag. Any field having this attribute indicates that its value is dependent on another field whose name is the value of this attribute. This is applicable to associated inserts and aggregated updates.
parent This can be set for any field under a non-base-table tag. This signifies the name of the table-tag that contains the referred field. The 'src' and 'parent' attributes together form an aggregated update request. If parent attribute is not present then the immediate parent of the current table is considered. This is applicable to aggregated updates.
id This can be set on any tuple. This represents a unique ID of a tuple and is applicable only for associated updates.
ref This can be set on any field. Any field having this attribute indicates that its value is dependent on another field which is present in a tuple whose ID is the value of this attribute. This is applicable only for associated updates. If the 'src' attribute is not present at this field then 'ref' has no significance.



Note:

  • If a field has data, the presence or absence of the above defined attributes do not have any significance.
  • For update operation in aggregated updates (when old and new tags are present in the request), the nested table structures under theoldandnewtags must be similar.

    For example, consider three tables PARENT, CHILD, and GRANDCHILD. The primary key for all the tables is the column 'ID' and it takes GUID. The format of GUID is {7F892C25-0E2A-4475-8022-BE8DD70426F6}.
  • 'PARENT' has fields - 'ID', 'DATA'
  • 'CHILD' has fields - 'ID', 'PARENT_ID', 'DATA'
  • 'GRANDCHILD' has fields - 'ID', 'GRANDPARENT_ID', 'PARENT_ID', 'DATA'

    Consider an insert in 'PARENT', 'CHILD' and 'GRANDCHILD' tables where
  • ID in 'PARENT' = PARENT_ID of 'CHILD'
  • PARENT_ID in 'GRANDCHILD' = ID of 'CHILD'
  • GRANDPARENT _ID in 'GRANDCHILD' = ID of 'PARENT'

    Using an aggregated update, the request will be as follows: 
    <tuple xmlns="">
    <new>
        <PARENT>
            <ID guid="true"/>
            <DATA> Hello World </DATA>
            <CHILD>
                <ID guid="true"/>
                <PARENT_ID src="ID"/>
                <DATA>Hello World</DATA>
                <GRANDCHILD>
                    <ID guid="true"/>
                    <PARENT_ID src="ID"/>
                    <GRANDPARENT_ID parent="PARENT" src="ID"/>
                    <DATA>Hello World</DATA>
                </GRANDCHILD>
            </CHILD>
        </PARENT>
    </new>
</tuple>


    Note:

  1. It is important to set the parent attribute at the GRANDPARENT_ID element tag, as it determines the PARENT of the referred field. If it is not present then the immediate parent (in this case, CHILD) is considered.
  2. The table tags in the update request are executed in the following order:
    • PARENT
    • CHILD
    • GRANDCHILD
  3. The referred field's table tag (for example, ID of PARENT in this case) should be processed before it is referenced by any another field (for example, PARENT_ID of CHILD).


A request using associated insert will be as follows:

<tuple id="1"> <new> <PARENT> <ID guid="true"/> <DATA> Hello World </DATA> </PARENT> </new> </tuple> <tuple id="2"> <new> <CHILD> <ID guid="true"/> <PARENT_ID ref="1" src="ID"/> <DATA>Hello World</DATA> </CHILD> </new> </tuple> <tuple> <new> <GRANDCHILD> <ID guid="true"/> <PARENT_ID ref="2" src="ID"/> <GRANDPARENT_ID ref="1" src="ID"/> <DATA>Hello World</DATA> </GRANDCHILD> </new> </tuple>


Note:

  1. For an associated insert, both ref and src attributes are mandatory.
  2. Association is restricted to insert operations only.
  3. The table tags in the update request are executed in the following order :
    • PARENT
    • CHILD
    • GRANDCHILD
  4. The referred field's table tag (for example, ID of PARENT in this case) should be processed before it is referenced by any another field (for example, GRANDPARENT_ID of GRANDCHILD).


A request using aggregation and association will be as follows:

<tuple id="1">
        <new>
            <PARENT>
                <ID guid="true"/>
                <DATA> Hello World </DATA>
            </PARENT>
        </new>
    </tuple>
    <tuple>
        <new>
            <CHILD>
                <ID guid="true"/>
                <PARENT_ID ref="1" src="ID"/>
                <DATA>Hello World</DATA>
                <GRANDCHILD>
                    <ID guid="true"/>
                    <PARENT_ID src="ID"/>
                    <GRANDPARENT_ID ref="1" src="ID"/>
                    <DATA>Hello World</DATA>
                </GRANDCHILD>
            </CHILD>
        </new>
    </tuple>


Note:

  1. The referred field (for example, ID field of table PARENT in this case) can be referenced using association only if it is under the base-table-tag . This implies that 'GRANDPARENT_ID' can never be a referred field for association.
  2. The table tags in the update request are executed in the following order:
    • PARENT
    • CHILD
    • GRANDCHILD


The following is an example of an update request using aggregated update operation:

<tuple>
    <old>
        <PARENT>
            <ID>\{F2DD4E72-2A87-4057-B9DA-75E6095FE838\}</ID>
            <DATA> Hello World </DATA>
            <CHILD>
                <ID>\{EF4CB848-8027-445F-8173-B5BEAD21011A\}</ID>
                <PARENT_ID src="ID"/>
                <DATA>Hello World</DATA>
                <GRANDCHILD>
                    <ID>\{6325BC11-F5C1-4C73-899B-52505D966CF0\}</ID>
                    <PARENT_ID src="ID"/>
                    <GRANDPARENT_ID parent="PARENT" src="ID"/>
                    <DATA>Hello World</DATA>
                </GRANDCHILD>
            </CHILD>
        </PARENT>
    </old>
    <new>
        <PARENT>
            <ID>\{F2DD4E72-2A87-4057-B9DA-75E6095FE838\}</ID>
            <DATA> Have A Nice Day </DATA>
            <CHILD>
                <ID>\{EF4CB848-8027-445F-8173-B5BEAD21011A\}</ID>
                <PARENT_ID src="ID"/>
                <DATA> Have A Nice Day </DATA>
                <GRANDCHILD>
                    <ID>\{6325BC11-F5C1-4C73-899B-52505D966CF0\}</ID>
                    <PARENT_ID src="ID"/>
                    <GRANDPARENT_ID parent="PARENT" src="ID"/>
                    <DATA>Have A Nice Day</DATA>
                </GRANDCHILD>
            </CHILD>
        </PARENT>
    </new>
</tuple>


Note:

  • The table structures underoldandnewtags are similar in an update request.
  • No field under thenewtag refers to a field under theoldtag.

Handling Special Characters in table or field names

Sometimes database elements (such as the table name or names of fields) may contain special characters that do not comply with the permitted names of XML elements. In several cases in the protocol used for database connectors, the XML element name is the same as the database element name. Therefore, the connector needs to support special characters in the protocol too.

To enable the special character support while configuring application connectors, users should select the Support Special Characters in XML check box. Once this is done, the connector allows the special characters to be encoded/decoded in the protocol.

The encoding that takes place for the special characters is as follows:

For each character in the name which is one of the special characters in XML {#,$, ,@}, the character is replaced by its hexa-decimal value in 4 digits placed between 'x' and '''.

For example,

  • ' # ' is replaced by 'x0023', where '23' is the hexa decimal value for '#'
  • '$' is replaced by 'x0024', where '24' is the hexa decimal value for '$'
  • ' ' replaced by 'x0020', where '20' is the hexa decimal value for ' '
  • '@' is replaced by 'x0040' where '40' is the hexa decimal value for '@'


Therefore, when the response is received, it needs to be decoded in the following way:

Each name is searched for the sequence 'xHHHH' where H is any character. If found, the entire sequence is replaced by the ANSI value of 'HHHH'.
Consider an example, where a table with the name 'Table Space$#' exists in the RDBMS, with columns 'Prime Key$# 1' , 'Col 1' , 'Col#2' , 'Prime Key$# 2'. The constructor tag in the implementation would be as follows:

<constructor language="DBSQL" xmlns="">
    <query>select "Prime Key$# 1" , "Col 1" , "Col#2" , "Prime Key$# 2" from "Table Space$#" where "Table Space$#"."Prime Key$# 2" = :PKEY</query>
    <parameters>
        <PKEY dd="Table Space$#.Prime Key$# 2">7</PKEY>
    </parameters>
</constructor>


The response of the above code would be as follows:

<tuple>
    <old>
        <Table_x0020_Space_x0024__x0023_>
            <Prime_x0020_Key_x0024__x0023__x0020_1>1</Prime_x0020_Key_x0024__x0023__x0020_1>
            <Col_x0020_1>Hello</Col_x0020_1>
            <Col_x0023_2>World</Col_x0023_2>
            <Prime_x0020_Key_x0024__x0023__x0020_2>7</Prime_x0020_Key_x0024__x0023__x0020_2>
        </Table_x0020_Space_x0024__x0023_>
    </old>
</tuple>


For all updates, insertions, or deletions using the connector, the user needs to send the names of the elements in the encoded format, as the connector will internally decode the names.

Note:

  • The allowed special character set is {#,$, ,@}.
  • XML encoding adds overhead to the WS-AppServer connector and hence may affect its performance.


Handling Views

The WS-AppServer connector also supports Read and Metadata operations on views.

Also, the request should specify views instead of tables to indicate that the operation is to be performed on a view.
For example, consider a view with special characters 'View $# 01'. Let the view be defined as follows:

CREATE view 'View $# 01' as select * from 'Table Space$#'

Consider that XML encoding is enabled for the connector. The following is the constructor tag in the implementation.

<constructor language="JDBCVIEW" xmlns="">
    <query>select v1.* from "View $# 01" v1 where v1."Prime Key$# 2" = :PKEY</query>
    <parameters>
        <PKEY dd="View $# 01.Prime Key$# 2" dt="i4">7</PKEY>
    </parameters>
</constructor>

The response would be as follows:

<tuple>
    <old>
        <View_x0020__x0024__x0023__x0020_01>
            <Prime_x0020_Key_x0024__x0023__x0020_1>1</Prime_x0020_Key_x0024__x0023__x0020_1>
            <Prime_x0020_Key_x0024__x0023__x0020_2>7</Prime_x0020_Key_x0024__x0023__x0020_2>
            <Col_x0020_1>dfg</Col_x0020_1>
            <Col_x0023_2>sgf</Col_x0023_2>
            <Col_x0024_3>asdf</Col_x0024_3>
        </View_x0020__x0024__x0023__x0020_01>
    </old>
</tuple>


Stored Procedures

Stored procedures are a saved collection of SQL statements that can take and return user-supplied parameters. Stored procedures can be of type FUNCTION, which can return a value mandatorily and PROCEDURE, which will not return a value.
An example of a stored procedure in MS SQL server, which takes two numbers and returns their sum, is as follows:

CREATE PROCEDURE AddData (@param1 int, @param2 int, @param3 int OUTPUT) AS SET @param3 = @param1 + @param


Here, attributes param1 and param2 are input parameters (type INPUT) and param3 is the parameter returned (type INPUTOUTPUT).
The implementation for calling the above stored procedure will be as follows:

<implementation type="DBSQL">
    <constructor language="DBRPC">
        <query>:RETURN_VALUE=AddData(:param1,:param2,:param3)</query>
        <parameters/>
    </constructor>
</implementation>


In the above implemenattion,RETURN_VALUE(type RETURN) indicates that the stored procedure is of type FUNCTION, and AddData indicates the name of the procedure invoked, and param,param2, and param3 are the parameters passed to the procedure. Since param3 is of type INPUTOUTPUT, a dummy value has to be passed as an input to the procedure call.
The request for executing the above sample stored procedure is as follows:

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
        <AddData xmlns="http://schemas.cordys.com/AddData">
            <RETURN_VALUE>0</RETURN_VALUE>
            <param1>5</param1>
            <param2>7</param2>
            <param3>1</param3>
        </AddData>
    </SOAP:Body>
</SOAP:Envelope>


Here, any number can be passed to the parameters RETURN_VALUE and param

3. If the procedure is successfully executed, it returns the value 0, else it returns the value
1. The parameters param1 and param2 are passed on to the procedure.

The response of the above message is as follows: 

<data>
    <AddDataResponse xmlns="http://schemas.cordys.com/1.0/demo/northwind">
        <tuple>
            <old>
                <AddData>
                    <RETURN_VALUE>0</RETURN_VALUE>
                    <param3>12</param3>
                </AddData>
            </old>
        </tuple>
    </AddDataResponse>
</data>


Stored procedures can have input/output parameters of type NUMERIC. These will return both the integer part and the decimal part. Stored procedures can also have input/output parameters of type TIME/DATE.

Oracle stored procedures can have input and output parameters of type CHARACTER. Here, in the implementation of the Web service operation, character parameters should have an attribute calledsize, which specifies the number of characters present in the parameter value. If the implementation does not have thesizeattribute, a default size is used.

The following is the implementation of a Web service operation for executing a stored procedure in Oracle:

<implementation type="DBSQL">
    <constructor language="DBRPC">
        <query>EmpProcedure6(:paramIn,:paramOut)</query>" <parameters>
            <paramIn size="20"/>
            <paramOut size="100"/>
        </parameters>
    </constructor>
</implementation>


Oracle stored procedures can be executed using OraOLEDB.Oracle and MSADORA providers.

Note: It is recommended to use native OLE DB providers (like SQLOLEDB, OraOLEDB.Oracle, etc.) for stored procedure execution.

Invoking Stored Procedures from the front end

The following is an sample stored procedure, CustOrdersOrders, which is defined in the Web service interface Web service interface Northwind and returns a list of orders for a particular customer ID is given below:

<implementation type="DBSQL">
    <constructor language="DBRPC">
        <query>:RETURN_VALUE=CustOrdersOrders(:CustomerID)</query>
        <parameters/>
    </constructor>
</implementation>


The calling request for such a stored procedure is as follows:

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
        <CustOrdersOrders xmlns="http://schemas.cordys.com/demo/northwind">
            <RETURN_VALUE>0</RETURN_VALUE>
            <CustomerID>TOMSP</CustomerID>
        </CustOrdersOrders>
    </SOAP:Body>
</SOAP:Envelope>

The following is a sample response for the above stored procedure when the customer ID is 'TOMSP.

<data>
    <CustOrdersOrdersResponse xmlns="http://schemas.cordys.com/demo/northwind">
        <tuple>
            <old>
                <rowset>
                    <row>
                        <OrderID>10249</OrderID>
                        <OrderDate>1996-07-05T00:00:00.0</OrderDate>
                        <RequiredDate>1996-08-16T00:00:00.0</RequiredDate>
                        <ShippedDate>1996-07-10T00:00:00.0</ShippedDate>
                    </row>
                    <row>
                        <OrderID>10438</OrderID>
                        <OrderDate>1997-02-06T00:00:00.0</OrderDate>
                        <RequiredDate>1997-03-06T00:00:00.0</RequiredDate>
                        <ShippedDate>1997-02-14T00:00:00.0</ShippedDate>
                    </row>
                    <row>
                        <OrderID>10446</OrderID>
                        <OrderDate>1997-02-14T00:00:00.0</OrderDate>
                        <RequiredDate>1997-03-14T00:00:00.0</RequiredDate>
                        <ShippedDate>1997-02-19T00:00:00.0</ShippedDate>
                    </row>
                    <row>
                        <OrderID>10548</OrderID>
                        <OrderDate>1997-05-26T00:00:00.0</OrderDate>
                        <RequiredDate>1997-06-23T00:00:00.0</RequiredDate>
                        <ShippedDate>1997-06-02T00:00:00.0</ShippedDate>
                    </row>
                </rowset>
            </old>
        </tuple>
        <tuple>
            <old>
                <CustOrdersOrders>
                    <RETURN_VALUE>0</RETURN_VALUE>
                </CustOrdersOrders>
            </old>
        </tuple>
    </CustOrdersOrdersResponse>
</data>

Obtaining Stored Procedure Responses in Process Platform Protocol Format

Additionally, stored procedure requests can also contain the 'layout' tag in the SOAP Web service operation implementation. This tag is used when the response of the stored procedure is required in the Process Platform protocol format . When this tag is used, a set of business object names can be specified as XML elements under thelayouttag. Each row in the result set will then be encapsulated within the specified business object.

Note: If the number of returned result sets exceeds the specified number of business objects under the layout tag, the connector will name the business objects as TABLE, TABLE1, TABLE2 and so on.
Consider the following example:

<implementation type="DBSQL">
    <constructor language="DBRPC">
        <query>:RETURN_VALUE=CustOrdersOrders( :CustomerID)</query>
        <parameters/>
        <layout>
            <Customers/>
        </layout>
    </constructor>
</implementation>


The request for such a stored procedure call is as follows:

<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP:Body>
        <CustOrdersOrders xmlns="http://schemas.cordys.com/demo/northwind">
            <RETURN_VALUE>0</RETURN_VALUE>
            <CustomerID>TOMSP</CustomerID>
        </CustOrdersOrders>
    </SOAP:Body>
</SOAP:Envelope>


The following is a sample response for the above stored procedure when the customer ID is TOMSP:

<data>
    <CustOrdersOrdersResponse xmlns="http://schemas.cordys.com/demo/northwind">
        <tuple>
            <old>
                <Customers>
                    <OrderID>10249</OrderID>
                    <OrderDate>1996-07-05T00:00:00.0</OrderDate>
                    <RequiredDate>1996-08-16T00:00:00.0</RequiredDate>
                    <ShippedDate>1996-07-10T00:00:00.0</ShippedDate>
                </Customers>
            </old>
        </tuple>
        <tuple>
            <old>
                <Customers>
                    <OrderID>10438</OrderID>
                    <OrderDate>1997-02-06T00:00:00.0</OrderDate>
                    <RequiredDate>1997-03-06T00:00:00.0</RequiredDate>
                    <ShippedDate>1997-02-14T00:00:00.0</ShippedDate>
                </Customers>
            </old>
        </tuple>
        <tuple>
            <old>
                <Customers>
                    <OrderID>10446</OrderID>
                    <OrderDate>1997-02-14T00:00:00.0</OrderDate>
                    <RequiredDate>1997-03-14T00:00:00.0</RequiredDate>
                    <ShippedDate>1997-02-19T00:00:00.0</ShippedDate>
                </Customers>
            </old>
        </tuple>
        <tuple>
            <old>
                <Customers>
                    <OrderID>10548</OrderID>
                    <OrderDate>1997-05-26T00:00:00.0</OrderDate>
                    <RequiredDate>1997-06-23T00:00:00.0</RequiredDate>
                    <ShippedDate>1997-06-02T00:00:00.0</ShippedDate>
                </Customers>
            </old>
        </tuple>
        <tuple>
            <old>
                <CustOrdersOrders>
                    <RETURN_VALUE>0</RETURN_VALUE>
                </CustOrdersOrders>
            </old>
        </tuple>
    </CustOrdersOrdersResponse>
</data>

Creating SOAP Web service operations

The Web service operations should have either thedtorddattribute for each parameter in the implementation.

For example:

<implementation type="DBSQL">
    <constructor language="DBSQL">
        <query>select * from EMP where EMPNO=:EMPNO</query>
        <parameters>
            <EMPNO dd="EMP.EMPNO" dt="i4"/>
        </parameters>
    </constructor>
</implementation>


The dt and dd attributes provide information about the parameter data types. When the request is processed, the data type is retrieved as follows:

  • It first checks for the dt attribute. If the dt attribute exists and has a non-empty value, that value is taken as the parameter data type.
  • If dt does not exist or has an empty value, it checks for the dd attribute. If the dd attribute exists and has a non-empty value, that value is taken as the parameter data type.
  • If the dd attribute does not exist or has an empty value, then 'string' is taken as the parameter data type.


Note: It is recommended to use the dt attribute in order to obtain optimal performance. Use of the dt attribute improves performance since the database need not be checked for the parameter data type definition, and hence the responses are faster.

Related information

WS-AppServer APIs